import pymssql


class SqlServerClientBase:
    def __init__(self, server):
        self.connections = {}  # 存储数据库连接的字典
        conn = pymssql.connect(
            server=server['host'],
            port=server['port'],
            user=server['username'],
            password=server['password'],
            database=server['database'], as_dict=True, tds_version="7.0"
        )
        self.database = server['database']
        self.connections[server["database"]] = conn

    def execute_query(self, query=None, params=None):
        with self.connections[self.database].cursor() as cursor:  # 获取游标(cursor)
            cursor.execute(query, params)  # 执行查询语句
            result = cursor.fetchall()  # 获取结果集
        return result

    def execute_non_query(self, query=None, params=None):
        with self.connections[self.database].cursor() as cursor:  # 获取游标(cursor)
            cursor.execute(query, params)  # 执行增删改语句
            self.connections[self.database].commit()  # 提交事务
            return cursor.rowcount  # 返回受影响的行数

    def execute_query_all(self, servers, query, params=None):
        results = {}  # 存储所有查询结果的字典
        for server in servers:
            connection_name = server['name']
            results[connection_name] = self.execute_query(connection_name, query, params)
        return results

    def execute_non_query_all(self, connection_name="Fits2Test", query=None, params=None):
        try:
            res = self.execute_non_query(connection_name, query, params)
            return res
        except Exception as e:
            print(f"Error occurred when running query on {connection_name}: {e}")
            self.connections[connection_name].rollback()  # 出错时回滚
        return None

    # 批量插入
    def execute_many(self, query=None, params=None):
        try:
            cursor = self.connections[self.database].cursor()
            cursor.executemany(query, params)
            self.connections[self.database].commit()  # 提交事务
            return cursor.rowcount
        except Exception as e:
            print(f"Error occurred when running query on {self.database}: {e}")
            self.connections[self.database].rollback()  # 出错时回滚
        return 0
